﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.IO;
using System.Data;
using System.Text.RegularExpressions;

namespace Liju.Common
{
    public class ExcelHelper
    {

        /// <summary>
        /// DateGridView导出到csv格式的Excel,通用  
        /// </summary>
        /// <param name="dgv"></param>
        public static void ExportToCSV(DataGridView dgv)
        {
            string filePath;
            SaveFileDialog saveFileDialog = new SaveFileDialog();
            saveFileDialog.Filter = "CSV files (*.csv)|*.csv";
            saveFileDialog.FilterIndex = 0;
            if (saveFileDialog.ShowDialog() == DialogResult.OK)
            {
                filePath = saveFileDialog.FileName;
            }
            else
            {
                return;
            }
            using (StreamWriter sw = new StreamWriter(filePath))
            {
                // 写入列标题
                string headers = string.Join(",", dgv.Columns.Cast<DataGridViewColumn>().Select(column => "\"" + column.HeaderText + "\"").ToArray());
                sw.WriteLine(headers);

                // 写入数据行
                foreach (DataGridViewRow row in dgv.Rows)
                {
                    string line = string.Join(",", row.Cells.Cast<DataGridViewCell>().Select(cell => "\"" + cell.Value?.ToString().Replace("\"", "\"\"") + "\"").ToArray());
                    sw.WriteLine(line);
                }
            }
        }

        /// <summary>
        /// 将可见的DataGridView赋给DataTable
        /// </summary>
        /// <param name="dgv"></param>
        /// <returns></returns>
        public static DataTable DataGridViewToDataTable(DataGridView dgv)
        {
            DataTable dt = new DataTable();
            //添加列
            for (int i = 0; i < dgv.Columns.Count; i++)
            {
                if (dgv.Columns[i].Visible)
                {
                    dt.Columns.Add(dgv.Columns[i].HeaderText);
                }
                //dt.Columns.Add(dgv.Columns[i].Name);
            }
            //添加行
            for (int j = 0; j < dgv.Rows.Count; j++)
            {
                DataRow dr = dt.NewRow();
                int h = 0;
                for (int k = 0; k < dgv.Columns.Count; k++)
                {
                    //dr[k] = dgv.Rows[j].Cells[k].Value?.ToString();
                    if (dgv.Columns[k].Visible)
                    {
                        dr[h] = dgv.Rows[j].Cells[k].Value?.ToString();
                        h++;
                    }
                }
                dt.Rows.Add(dr);
            }
            return dt;
        }
        /// <summary>
        /// List<T> to DataTable
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        /// <returns></returns>
        public static DataTable ListToDataTable<T>(List<T> list)
        {
            DataTable dataTable = new DataTable(typeof(T).Name);
            var properties = typeof(T).GetProperties();
            foreach (var property in properties)
            {
                dataTable.Columns.Add(property.Name, property.PropertyType);
            }
            foreach (T item in list)
            {
                var values = new object[properties.Length];
                for (int i = 0; i < properties.Length; i++)
                {
                    values[i] = properties[i].GetValue(item, null);
                }
                dataTable.Rows.Add(values);
            }
            return dataTable;
        }
        /// <summary>
        /// 导出 DataTable to csv
        /// </summary>
        /// <param name="dt"></param>
        public static void ExportDataTableToCSV(string filePath, params DataTable[] dataTables)
        {
            using (var writer = new StreamWriter(filePath, false, new UTF8Encoding(true)))
            {
                foreach (var dt in dataTables)
                {
                    // 写入列标题
                    var columns = string.Join(",", dt.Columns.Cast<DataColumn>().Select(column => $"\"{column.ColumnName}\""));
                    writer.WriteLine($"\"Table:{dt.TableName}\",{columns}");
                    //写入数据
                    foreach (DataRow row in dt.Rows)
                    {
                        var values = string.Join(",", row.ItemArray.Select(field => $"\"{field.ToString().Replace("\"", "\"\"")}\""));
                        writer.WriteLine($"\"\",{values}");
                    }
                    writer.WriteLine();
                }
            }
        }
        /// <summary>
        /// 导入 Csv to Tables
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public static DataTable[] ImportCsvToTables(string filePath)
        {
            DataTable[] tables = new DataTable[0];
            using (var reader = new StreamReader(filePath, Encoding.UTF8))
            {
                string line;
                DataTable currentTable = null;
                string currentTableIdentifier = null;
                while ((line = reader.ReadLine()) != null)
                {
                    //使用正则表达式匹配表标识符和数据
                    var match = Regex.Match(line, @"""(Table:\w+Model)"",(.*)");
                    if (match.Success)
                    {
                        currentTableIdentifier = match.Groups[1].Value.Substring("Table:".Length).Trim();
                        var columns = match.Groups[2].Value.Split(new[] { ',' }, StringSplitOptions.None).
                            Select(column => column.Trim('"')).ToArray();
                        currentTable = new DataTable(currentTableIdentifier);
                        foreach (var column in columns)
                        {
                            currentTable.Columns.Add(column, typeof(string));
                        }
                        tables = AddTable(tables, currentTable);
                    }
                    else if (currentTable != null && !string.IsNullOrWhiteSpace(line))
                    {
                        var values = line.Split(new[] { ",\"" }, StringSplitOptions.None).Select(value => value.Trim('"')).ToArray();
                        var row = currentTable.NewRow();
                        for (int i = 1; i < values.Length; i++)
                        {
                            row[currentTable.Columns[i - 1]] = values[i];
                        }
                        currentTable.Rows.Add(row);
                    }
                }
            }
            return tables;
        }
        /// <summary>
        /// 添加表
        /// </summary>
        /// <param name="tables"></param>
        /// <param name="newTable"></param>
        /// <returns></returns>
        private static DataTable[] AddTable(DataTable[] tables, DataTable newTable)
        {
            var newList = new List<DataTable>(tables);
            newList.Add(newTable);
            return newList.ToArray();
        }
        /// <summary>
        /// DateGridView导出到csv格式的Excel
        /// hideSaveFlag:隐藏列是否导出
        /// </summary>
        /// <param name="dgv"></param>
        public static void ExportToCSV(DataGridView dgv, bool hideSaveFlag = true)
        {
            string filePath;
            SaveFileDialog saveFileDialog = new SaveFileDialog();
            saveFileDialog.Filter = "CSV files (*.csv)|*.csv";
            saveFileDialog.FilterIndex = 0;
            if (saveFileDialog.ShowDialog() == DialogResult.OK)
            {
                filePath = saveFileDialog.FileName;
            }
            else
            {
                return;
            }
            using (StreamWriter sw = new StreamWriter(filePath))
            {
                if (!hideSaveFlag)
                {
                    DataTable newTable = DataGridViewToDataTable(dgv);

                    // 写入列标题
                    string headers = string.Join(",", newTable.Columns.Cast<DataColumn>().Select(column => "\"" + column.Caption + "\"").ToArray());
                    sw.WriteLine(headers);

                    // 写入数据行
                    foreach (DataRow row in newTable.Rows)
                    {
                        string line = string.Join(",", row.ItemArray.ToList().Select(c => "\"" + c.ToString().Replace("\"", "\"\"") + "\"").ToArray());
                        sw.WriteLine(line);
                    }
                }
                else
                {
                    // 写入列标题
                    string headers = string.Join(",", dgv.Columns.Cast<DataGridViewColumn>().Select(column => "\"" + column.HeaderText + "\"").ToArray());
                    sw.WriteLine(headers);

                    // 写入数据行
                    foreach (DataGridViewRow row in dgv.Rows)
                    {
                        string line = string.Join(",", row.Cells.Cast<DataGridViewCell>().Select(cell => "\"" + cell.Value?.ToString().Replace("\"", "\"\"") + "\"").ToArray());
                        sw.WriteLine(line);
                    }
                }
            }
        }

        /// <summary>
        /// 使用Microsoft.Office.Interop.Excel保存，可能出现不同版本兼容性问题
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="myDGV"></param>
        public static void ExportToExcel(DataGridView myDGV)
        {
            string saveFileName = "";
            SaveFileDialog saveDialog = new SaveFileDialog();
            saveDialog.DefaultExt = "xls";
            saveDialog.Filter = "Excel文件|*.xls";
            saveDialog.FileName = "导出excel数据";
            saveDialog.ShowDialog();
            saveFileName = saveDialog.FileName;
            if (saveFileName.IndexOf(":") < 0) return; //被点了取消
            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            if (xlApp == null)
            {
                MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
                return;
            }
            Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
            Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
                                                                                                                                  //写入标题
            for (int i = 0; i < myDGV.ColumnCount; i++)
            {

                worksheet.Cells[1, i + 1] = myDGV.Columns[i].HeaderText;
            }

            //写入数值
            for (int r = 0; r < myDGV.Rows.Count; r++)
            {
                for (int i = 0; i < myDGV.ColumnCount; i++)
                {
                    worksheet.Cells[r + 2, i + 1] = myDGV.Rows[r].Cells[i].Value;
                }
                System.Windows.Forms.Application.DoEvents();
            }
            worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
            if (saveFileName != "")
            {
                try
                {
                    workbook.Saved = true;
                    workbook.SaveCopyAs(saveFileName);
                }
                catch (Exception ex)
                {
                    MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
                }
            }
            xlApp.Quit();
        }




    }
}
